﻿using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.IT;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.TD;
using Coldairarrow.IBusiness.Report;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using OracleInternal.SqlAndPlsqlParser.LocalParsing;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace Coldairarrow.Business.IT
{
    public partial class Report_MaterialSummaryBussiness : BaseBusiness<IT_LocalMaterial>, IReport_MaterialSummaryBussiness, ITransientDependency
    {
        readonly IServiceProvider _ServiceProvider;
        public Report_MaterialSummaryBussiness(IDbAccessor db, IServiceProvider svc)
            : base(db)
        {
            _ServiceProvider = svc;
        }
        public async Task<PageResult<Report_MaterialSummaryVM>> GetDataListAsync(PageInput<Report_MaterialSummaryQM> input)
        {
            var search = input.Search;
            var mQuery = from lm in Db.GetIQueryable<IT_LocalMaterial>()
                         group lm by new { lm.MaterialId } into g
                         select new { MaterialId = g.Key.MaterialId, SumCount = g.Sum(p => p.Nums_N) } into lms
                         join m in Db.GetIQueryable<PB_Material>() on lms.MaterialId equals m.Id
                         join mt in Db.GetIQueryable<PB_MaterialType>() on m.MaterialTypeId equals mt.Id
                         join me in Db.GetIQueryable<PB_Measure>() on m.UnitGroupId equals me.Id
                         select new Report_MaterialSummaryVM
                         {
                             Id = m.Id,
                             Name = m.Name,
                             Code = m.Code,
                             BarCode = m.BarCode,
                             SimpleName = m.SimpleName,
                             MaterialTypeId = m.MaterialTypeId,
                             MaterialId = m.Id,
                             MaterialName = m.Name,
                             MaterialTypeName = mt.Name,
                             UnitGroupId = me.Id,
                             MeasureName = me.Name,
                             Spec = m.Spec,
                             Max = m.Max,
                             Min = m.Min,
                             Price = m.Price,
                             SumCount = lms.SumCount
                         };
            if (!search.MaterialTypeId.IsNullOrEmpty())
                mQuery = mQuery.Where(w => w.MaterialTypeId == search.MaterialTypeId);
            if (!search.MaterialName.IsNullOrEmpty())
                mQuery = mQuery.Where(w => w.Name.Contains(search.MaterialName) || w.Code.Contains(search.MaterialName) || w.SimpleName.Contains(search.MaterialName) || w.BarCode.Contains(search.MaterialName));
            if (search.MinAlert)
                mQuery = mQuery.Where(w => w.Min >= w.SumCount && w.Min.HasValue);
            if (search.MaxAlert)
                mQuery = mQuery.Where(w => w.Max <= w.SumCount && w.Max.HasValue);
            if (!search.BatchNo.IsNullOrEmpty())
                mQuery = mQuery.Where(w => w.BatchNo == search.BatchNo);
            var pageResult = await mQuery.GetPageResultAsync(input);
            //var listbatch =
            foreach (var item in pageResult.Data)
            {
                item.Id = item.Id + "_" + item.BatchNo;
            }
            return pageResult;
        }

        /// <summary>
        /// 获取物料报表数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResultData<MaterialStatistics>> GetMaterialStatisticsList(PageInput<MaterialStatisticsSeach> input)
        {
            string sqlMaterialStatistics = "select a.Id MaterialId, a.`Name`,a.`Code`,b.`Name` TypeName,b.Id TypeId,a.ProductCode ProductCode,a.Spec Spec,c.`Name` StorageName, IFNULL( a.Min,0) Min, IFNULL(a.Max, 0) Max ,	IFNULL(a.Weight,0) Weight,IFNULL(SUM(i.Num), 0) * IFNULL(a.Weight, 0)  AS GrossWeight, IFNULL(SUM(i.Num),0) AS Totalinventory  from PB_Material as a LEFT JOIN  PB_MaterialType as b ON a.MaterialTypeId = b.Id LEFT JOIN Base_UnitItems as c on a.storageId = c.Id LEFT JOIN IT_LocalDetail AS i on a.Id = i.MaterialId WHERE  1 = 1 {0}  GROUP BY  i.MaterialId,a.`Name`,a.`Code`,b.`Name`,a.ProductCode,a.Spec,c.`Name`,a.Min,a.Max,a.Id  	HAVING  SUM( i.Num )>0 ";
           
            string MaterOrLike = "";
            if (!input.Search.Name.IsNullOrEmpty()) {
                MaterOrLike = "AND a.`Name` LIKE '%"+input.Search.Name+"%' OR  a.`Code`  LIKE '%"+input.Search.Name+"%'";
            }
         
            sqlMaterialStatistics = string.Format(sqlMaterialStatistics,MaterOrLike);
            var res = await Db.GetListBySqlAsync<MaterialStatistics>(sqlMaterialStatistics);

            if (!input.Search.Name.IsNullOrEmpty()) {

              res= res.Where(s => s.Name.Contains(input.Search.Name)||s.Code.Contains(input.Search.Name)).ToList();
            }
            if (!input.Search.TypeName.IsNullOrEmpty())
            {

                List<string> typelist = new List<string>();
                string list = "WITH RECURSIVE cte AS (  SELECT* FROM PB_MaterialType WHERE Id = '" + input.Search.TypeName + "' UNION ALL  SELECT sm.*FROM PB_MaterialType sm, cte WHERE sm.ParentId = cte.id) SELECT ID FROM cte ORDER BY Id;  ";
                var types = await Db.GetListBySqlAsync<TypeIdList>(list.ToString());
                foreach (var item in types)
                {
                    typelist.Add(item.Id);
                }
                res = res.Where(s => typelist.Contains(s.TypeId)).ToList();
            }

            string sqlMaterialStatistics1 = "select SUM(T.GrossWeight) as GrossWeight FROM(SELECT a.Id MaterialId,  a.`Name`, a.`Code`, b.`Name` TypeName, a.ProductCode ProductCode, a.Spec Spec, c.`Name` StorageName,  IFNULL(a.Min, 0) Min,IFNULL(a.Max, 0) Max, IFNULL(a.Weight, 0) Weight, IFNULL(SUM(i.Num), 0) * IFNULL(a.Weight, 0)  AS GrossWeight, IFNULL(SUM(i.Num), 0) AS Totalinventory FROM PB_Material AS a  LEFT JOIN PB_MaterialType AS b ON a.MaterialTypeId = b.Id LEFT JOIN Base_UnitItems AS c ON a.storageId = c.Id LEFT JOIN IT_LocalDetail AS i ON a.Id = i.MaterialId WHERE 1 = 1 {0} {1} GROUP BY i.MaterialId,a.`Name`,a.`Code`,b.`Name`,a.ProductCode,a.Spec,c.`Name`,a.Min,a.Max,a.Id  {2} {3}) AS T";
            string TypeId1 = "";
            if (!input.Search.TypeName.IsNullOrEmpty())
            {
                TypeId1 = "AND a.MaterialTypeId='" + input.Search.TypeName + "'";
            }
            string MaterOrLike1 = "";
            if (!input.Search.Name.IsNullOrEmpty())
            {
                MaterOrLike1 = "AND a.`Name` LIKE '%" + input.Search.Name + "%' OR  a.`Code`  LIKE '%" + input.Search.Name + "%'";
            }
            string sqldi1 = "";
            if (input.Search.EarlyWarning == true)
            {
                sqldi1 = "HAVING SUM(i.Num)<a.Min";
            }
            string sqlgao1 = "";
            if (input.Search.GaoWarnig == true)
            {
                sqlgao1= "HAVING SUM(i.Num)>a.Max";
            }
            sqlMaterialStatistics1 = string.Format(sqlMaterialStatistics1, TypeId1, MaterOrLike1, sqldi1, sqlgao1);
            var rekss = await Db.GetListBySqlAsync<MaterialStatistics>(sqlMaterialStatistics1);





            int pagecount;//总页数
            List<MaterialStatistics> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<MaterialStatistics> productes = new List<MaterialStatistics>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
           
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResultData<MaterialStatistics> { Data = productes, Total = pagees, TotalWeight = rekss[0].GrossWeight };
        } 
    }
}